<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Working with images</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="C#, SQLite, ADO.NET, database, images, tutorial, learn SQLite C# ">
<meta name="description" content="In this part of the SQLite C# tutorial, we work with images. We insert an 
image to the database and retrieve it back.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">

<div>
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

</div>


<div class="content2">

<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Working with images</h1>

<p>
In this chapter of the SQLite C# tutorial, we will work with image files.
Note that some people oppose putting images into databases. Here we 
only show how to do it. We do not dwell into technical issues of whether to save images in
databases or not. 
</p>

<pre>
sqlite> CREATE TABLE Images(Id INTEGER PRIMARY KEY, Data BLOB);
</pre>

<p>
For this example, we create a new table called Images. For the images, we use
the <code>BLOB</code> data type, which stands for Binary Large Object. 
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<h2>Inserting images</h2>

<p>
In the first example, we are going to insert an image to the 
SQLite database. 
</p>

<pre class="code">
using System;
using System.IO;
using System.Data;
using Mono.Data.Sqlite;

public class Example
{
    static void Main() 
    {

        string cs = "URI=file:test.db";        

        using(SqliteConnection con = new SqliteConnection(cs))
        {
            
            con.Open();

            byte[] data = null;

            try
            {
                data = File.ReadAllBytes("woman.jpg");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
 
            SqliteCommand cmd = new SqliteCommand(con);
    
            cmd.CommandText = "INSERT INTO Images(Data) VALUES (@img)";
            cmd.Prepare();

            cmd.Parameters.Add("@img", DbType.Binary, data.Length);
            cmd.Parameters["@img"].Value = data;
            cmd.ExecuteNonQuery();
            
            con.Close();
        }
    }
}
</pre>

<p>
We read an image from the current working directory 
and write it into the Images table of the SQLite test.db database. 
</p>

<pre class="explanation">
byte[] data = null;
</pre>

<p>
The image data will be stored in an array of bytes. 
</p>

<pre class="explanation">
data = File.ReadAllBytes("woman.jpg");
</pre>

<p>
The <code>ReadAllBytes()</code> method opens a binary file, reads 
the contents of the file into a byte array, and then closes the file.
</p>

<pre class="explanation">
cmd.CommandText = "INSERT INTO Images(Data) VALUES (@img)";
cmd.Prepare();
</pre>

<p>
We prepare an SQL statement for inserting the array of bytes into the
Data column of the Images table. 
</p>

<pre class="explanation">
cmd.Parameters.Add("@img", DbType.Binary, data.Length);
cmd.Parameters["@img"].Value = data;
cmd.ExecuteNonQuery();
</pre>

<p>
We bind the binary data to the prepared statement. Then the 
statement is executed. The image is written to the database
table. 
</p>


<h2>Reading images</h2>

<p>
In this section, we are going to perform the reverse operation.
We will read an image from the database table. 
</p>

<pre class="code">
using System;
using System.IO;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";        

        using(SqliteConnection con = new SqliteConnection(cs))
        {            
            con.Open();

            SqliteCommand cmd = new SqliteCommand(con);   
            cmd.CommandText = "SELECT Data FROM Images WHERE Id=1";
            byte[] data = (byte[]) cmd.ExecuteScalar();

            try
            {               
                if (data != null)
                { 
                    File.WriteAllBytes("woman2.jpg", data);
                } else 
                {
                    Console.WriteLine("Binary data not read");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }            

            con.Close();
        }
    }
}
</pre>

<p>
We read image data from the Images table and write it
to another file, which we call woman2.jpg. 
</p>

<pre class="explanation">
cmd.CommandText = "SELECT Data FROM Images WHERE Id=1";
</pre>

<p>
This line selects the image data from the table.
</p>

<pre class="explanation">
byte[] data = (byte[]) cmd.ExecuteScalar();
</pre>

<p>
We retrieve the binary data from the database table. The
data is stored in an array of bytes. 
</p>

<pre class="explanation">
if (data != null)
{ 
    File.WriteAllBytes("woman2.jpg", data);
} else 
{
    Console.WriteLine("Binary data not read");
}
</pre>

<p>
The <code>WriteAllBytes()</code> method creates a new file, writes 
the specified byte array to the file, and then closes the file. 
If the target file already exists, it is overwritten.
When the database table is empty and we run this example, we get
a null. Therefore we check for the null value.
</p>

<p>
This part of the SQLite C# tutorial was dedicated to reading and writing 
images. 
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>

<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 16, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

